package com.gugee.tiktok.common.model.dal.blogger;

import com.gugee.tiktok.common.model.dto.SlimBloggerAiExtDto;
import com.gugee.tiktok.common.model.mysql.Blogger;
import com.gugee.tiktok.common.model.dto.SlimBloggerDto;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Optional;

@Repository
public interface BloggerRepository extends CrudRepository<Blogger, Long>, BloggerExtRepository {
    Optional<Blogger> findByoUid(long oUid);

    Optional<Blogger> findByoUniqueId(String uniqueId);

    @Query("SELECT id,o_uid as uid,o_region as region FROM blogger " +
            "WHERE id > :id " +
            "AND o_region != 'IN'"+
            " ORDER BY id ASC " +
            "LIMIT :count")
    List<SlimBloggerDto> findSlimBloggersByIdAndCount(long id, int count);
    @Query("SELECT * FROM blogger t " +
            "WHERE t.id BETWEEN :startId AND :endId AND t.o_follower_count >= :oFollowerCount  " +
            "AND t.o_region != 'IN' " +
            "AND not exists (select 1 from blogger_main b where b.o_uid = t.o_uid)")
    List<Blogger> findSlimBloggersByIdAndCount(int oFollowerCount,long startId,long endId);
    List<Blogger> findTop2000ByoFollowerCountGreaterThanAndIdGreaterThanOrderByIdAsc(int oFollowerCount, long id);


    boolean existsByoUid(long oUid);


    @Query("SELECT t.o_uid,CASE WHEN t.ai_gender = 0 THEN t.o_avatar300 ELSE '' END o_avatar300,t.ai_gender,t.tiktoker_status FROM blogger t " +
            " WHERE t.o_uid IN (:uids) " +
            " AND ( t.ai_gender != 0 OR (t.ai_gender = 0 " +
            " AND substring( o_avatar300, LOCATE( 'x-expires=', o_avatar300 ) + 10, 10 ) + 0 > :expires " +
            " ))")
    List<Blogger> findBloggersAiAnalysisByUidAndPicExpTime(List<Long> uids,long expires);

    /**
     * 更新博主性别、年龄、来源字段
     * @param aiAge
     * @param aiGender
     * @param genderSource
     * @param uid
     * @return
     */
    @Modifying
    @Query("UPDATE blogger t set t.ai_age = :aiAge,t.ai_gender = :aiGender,t.gender_source = :genderSource where t.o_uid = :uid ")
    int updateAiGenderAndAiAgeByoUid(@Param("aiAge") int aiAge, @Param("aiGender") int aiGender,
                                     @Param("genderSource") int genderSource,@Param("uid") long uid);

    /**
     * 查询博主男女、年龄比例
     * @param uids
     * @return
     */
    @Query("SELECT t.id,t.o_uid uid,t.ai_age,t.ai_gender,t.tiktoker_status," +
            " CASE WHEN t.ai_gender = 0 THEN t.o_avatar300 ELSE '' END avatar " +
            " FROM blogger t where t.o_uid in (:uids) ")
    List<SlimBloggerAiExtDto> findBloggersAiExtByInUid(List<Long> uids);

    /**
     * 查询最大的id
     * @return
     */
    @Query("SELECT MAX(id) from blogger ")
    long findMaxId();
}
